package com.eff.android.lib.helper;

import java.sql.SQLDataException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import com.eff.android.api.enums.Level;
import com.eff.android.api.enums.Subject;
import com.eff.android.api.enums.Type;
import com.eff.android.api.model.Model;
import com.eff.android.api.model.Setting;
import com.eff.android.config.EConstant;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

//DatabaseHelper.java
public class EDatabaseHelper extends SQLiteOpenHelper {
 
	static EDatabaseHelper dbHelper;
	
    // Table Create Statements
    // Table create statement
    private static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "
            + EConstant.TABLE + "(" + EConstant.KEY_ID_HINH + " TEXT PRIMARY KEY," + EConstant.KEY_VAN_BAN
            + " TEXT," + EConstant.KEY_Y_NGHIA + " TEXT," + EConstant.KEY_URI_HINH
            + " TEXT," + EConstant.KEY_PHIEN_AM_QUOC_TE + " TEXT," + EConstant.KEY_LOAI_HINH
            + " TEXT," + EConstant.KEY_LEVEL + " TEXT," + EConstant.KEY_MA_CHU_DE 
            + " TEXT," + EConstant.KEY_DATE_CREATE + " TEXT" + ")";
 
    public static EDatabaseHelper newInstance(Context context){
    	if (dbHelper != null)
    		return dbHelper;
    	
    	dbHelper = new EDatabaseHelper(context);
    	return dbHelper;
    }
    
    public EDatabaseHelper(Context context) {
        super(context, EConstant.DATABASE_NAME, null, EConstant.DATABASE_VERSION);
    }

	@Override
    public void onCreate(SQLiteDatabase db) {
 
        // creating required tables
        db.execSQL(CREATE_TABLE);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + EConstant.TABLE);
 
        // create new tables
        onCreate(db);
    }
    
    public Boolean CheckExistModel(String model_id) {
                
    	SQLiteDatabase db = this.getReadableDatabase();
        
        String selectQuery = "SELECT  * FROM " + EConstant.TABLE + " WHERE "
                + EConstant.KEY_ID_HINH + " = " + model_id;
        
        Log.e(EConstant.LOG, selectQuery);
     
        Cursor c = db.rawQuery(selectQuery, null);
     
        if (c != null)
                if (c.moveToFirst())
                        return true;
        
        return false;
    }
    
    /*
     * Creating a model
     */
    public long createModel(Model model) {
        
        if (!CheckExistModel(model.getIdHinh()))
        {
        	SQLiteDatabase db = this.getWritableDatabase();
            
            ContentValues values = new ContentValues();
            values.put(EConstant.KEY_ID_HINH, model.getIdHinh());
            values.put(EConstant.KEY_VAN_BAN, model.getVanBan());
            values.put(EConstant.KEY_Y_NGHIA, model.getYNghia());
            values.put(EConstant.KEY_URI_HINH, model.getUriHinh());
            values.put(EConstant.KEY_PHIEN_AM_QUOC_TE, model.getPhienAmQuocTe());
            values.put(EConstant.KEY_LOAI_HINH, model.getLoaiHinh());
            values.put(EConstant.KEY_LEVEL, model.getLevel());
            values.put(EConstant.KEY_MA_CHU_DE, model.getMaChuDe());
            values.put(EConstant.KEY_DATE_CREATE, model.getDateCreate());
             
            // insert row
            return db.insert(EConstant.TABLE, null, values);
             
        }
        
        return -1;
    }

    
    /*
     * get single model
     */
    public Model getModel(String model_id) {
        
    	Log.d("Ket qua tim : ", CheckExistModel(model_id).toString());
        
        if (CheckExistModel(model_id))
        {
                SQLiteDatabase db = this.getReadableDatabase();
             
                String selectQuery = "SELECT  * FROM " + EConstant.TABLE + " WHERE "
                        + EConstant.KEY_ID_HINH + " = " + model_id;
                
                Log.e(EConstant.LOG, selectQuery);
             
                Cursor c = db.rawQuery(selectQuery, null);
             
                if (c != null)
                    c.moveToFirst();
             
                Model md = new Model();
                md.setIdHinh(c.getString(c.getColumnIndex(EConstant.KEY_ID_HINH)));
                md.setVanBan(c.getString(c.getColumnIndex(EConstant.KEY_VAN_BAN)));
                md.setYNghia(c.getString(c.getColumnIndex(EConstant.KEY_Y_NGHIA)));
                md.setUriHinh(c.getString(c.getColumnIndex(EConstant.KEY_URI_HINH)));
                md.setPhienAmQuocTe(c.getString(c.getColumnIndex(EConstant.KEY_PHIEN_AM_QUOC_TE)));
                md.setLoaiHinh(c.getString(c.getColumnIndex(EConstant.KEY_LOAI_HINH)));
                md.setLevel(c.getString(c.getColumnIndex(EConstant.KEY_LEVEL)));
                md.setMaChuDe(c.getString(c.getColumnIndex(EConstant.KEY_MA_CHU_DE)));
                md.setDateCreate(c.getString(c.getColumnIndex(EConstant.KEY_DATE_CREATE)));
             
                return md;
        }
        
        return new Model("0", "0", "0", "0", "0", "0", "0", "0", "0");
    }
    
    private String setSelectQuery(int numberOfRecord, Subject id, Type type, Level level){    	
    	
    	switch(id){
	    	case Mixed:
	    		return "SELECT  * FROM " + EConstant.TABLE + " WHERE " + EConstant.KEY_LOAI_HINH 
	    				+ "='" + type.getValue() + "' AND " + EConstant.KEY_LEVEL + "='" + level.getValue() 
	    				+ "' ORDER BY RANDOM()" + "ASC LIMIT " + numberOfRecord;
	    	case People:
	    		return "SELECT  * FROM " + EConstant.TABLE + " WHERE " + EConstant.KEY_MA_CHU_DE + "= 50 AND " + 
	    			EConstant.KEY_LOAI_HINH + "='" + type.getValue() + "' AND " + EConstant.KEY_LEVEL + "='" + level.getValue() 
	    			+ "' ORDER BY RANDOM()" 
	    			+ " ASC LIMIT " + numberOfRecord;
	    	case Communication:
	    		return "SELECT  * FROM " + EConstant.TABLE + " WHERE " + EConstant.KEY_MA_CHU_DE + "= 55 AND " + 
    			EConstant.KEY_LOAI_HINH + "='" + type.getValue() + "' AND " + EConstant.KEY_LEVEL + "='" + level.getValue()
    			+ "' ORDER BY RANDOM()" 
    			+ " ASC LIMIT " + numberOfRecord;
	    	case nature:
	    		return "SELECT  * FROM " + EConstant.TABLE + " WHERE " + EConstant.KEY_MA_CHU_DE + "= 52 AND " + 
    			EConstant.KEY_LOAI_HINH + "='" + type.getValue() + "' AND " + EConstant.KEY_LEVEL + "='" + level.getValue()
    			+ "' ORDER BY RANDOM()" 
    			+ " ASC LIMIT " + numberOfRecord;
	    	case Activitive:
	    		return "SELECT  * FROM " + EConstant.TABLE + " WHERE " + EConstant.KEY_MA_CHU_DE + "= 56 AND " + 
    			EConstant.KEY_LOAI_HINH + "='" + type.getValue() + "' AND " + EConstant.KEY_LEVEL + "='" + level.getValue()
    			+ "' ORDER BY RANDOM()" 
    			+ " ASC LIMIT " + numberOfRecord;
	    	case Character:
	    		return "SELECT  * FROM " + EConstant.TABLE + " WHERE " + EConstant.KEY_MA_CHU_DE + "= 63 AND " + 
    			EConstant.KEY_LOAI_HINH + "='" + type.getValue() + "'AND " + EConstant.KEY_LEVEL + "='" + level.getValue()
    			+ "' ORDER BY RANDOM()" 
    			+ " ASC LIMIT " + numberOfRecord;
    	}
    	
    	return null;
    }
    
    /*
     * getting all models
     * */
    public List<Model> getAllModels(int numberOfRecord, Subject id, Type type, Level level) {
        
        List<Model> models = new ArrayList<Model>();
        String selectQuery = setSelectQuery(numberOfRecord, id, type, level);
     
        Log.e(EConstant.LOG, selectQuery);
     
        SQLiteDatabase db = this.getReadableDatabase();
        
	    Cursor c = db.rawQuery(selectQuery, null);
	     
	    if (c != null)
            {    
		    	// looping through all rows and adding to list
		        if (c.moveToFirst()) {
		            do {
		                Model md = new Model();
		                md.setIdHinh(c.getString(c.getColumnIndex(EConstant.KEY_ID_HINH)));
		                md.setVanBan(c.getString(c.getColumnIndex(EConstant.KEY_VAN_BAN)));
		                md.setYNghia(c.getString(c.getColumnIndex(EConstant.KEY_Y_NGHIA)));
		                md.setUriHinh(c.getString(c.getColumnIndex(EConstant.KEY_URI_HINH)));
		                md.setPhienAmQuocTe(c.getString(c.getColumnIndex(EConstant.KEY_PHIEN_AM_QUOC_TE)));
		                md.setLoaiHinh(c.getString(c.getColumnIndex(EConstant.KEY_LOAI_HINH)));
		                md.setLevel(c.getString(c.getColumnIndex(EConstant.KEY_LEVEL)));
		                md.setMaChuDe(c.getString(c.getColumnIndex(EConstant.KEY_MA_CHU_DE)));
		                md.setDateCreate(c.getString(c.getColumnIndex(EConstant.KEY_DATE_CREATE)));
		     
		             // adding to model list
		                models.add(md);
		            } while (c.moveToNext());
		        }
            }
        Log.d("Get All Models:", String.valueOf(models.size()));
        return models;  	
     
        /*
        List<Model> result = new ArrayList<Model>();
        return result; */
    }
    
    /*
     * getting all models under single EConstant.KEY_MA_CHU_DE
     * */
    public List<Model> getAllModelsByTag(String tag_name) {
        
        List<Model> listModels = new ArrayList<Model>();
     
        String selectQuery = "SELECT  * FROM " + EConstant.TABLE + " WHERE "
                + EConstant.KEY_MA_CHU_DE + " = " + tag_name;
     
        Log.e(EConstant.LOG, selectQuery);
     
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
     
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                Model md = new Model();
                md.setIdHinh(c.getString(c.getColumnIndex(EConstant.KEY_ID_HINH)));
                md.setVanBan(c.getString(c.getColumnIndex(EConstant.KEY_VAN_BAN)));
                md.setYNghia(c.getString(c.getColumnIndex(EConstant.KEY_Y_NGHIA)));
                md.setUriHinh(c.getString(c.getColumnIndex(EConstant.KEY_URI_HINH)));
                md.setPhienAmQuocTe(c.getString(c.getColumnIndex(EConstant.KEY_PHIEN_AM_QUOC_TE)));
                md.setLoaiHinh(c.getString(c.getColumnIndex(EConstant.KEY_LOAI_HINH)));
                md.setLevel(c.getString(c.getColumnIndex(EConstant.KEY_LEVEL)));
                md.setMaChuDe(c.getString(c.getColumnIndex(EConstant.KEY_MA_CHU_DE)));
                md.setDateCreate(c.getString(c.getColumnIndex(EConstant.KEY_DATE_CREATE)));
     
             // adding to model list
                listModels.add(md);
            } while (c.moveToNext());
            
            return listModels;
        }
     
        List<Model> result = new ArrayList<Model>();
        return result;
    }
    
    /**
     * getting model count
     */
    public int getModelCount() {

        String countQuery = "SELECT  * FROM " + EConstant.TABLE;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
 
        int count = cursor.getCount();
        cursor.close();
 
        // return count
        return count;
    }
    
    /*
     * Updating a model
     */
    public long updateModel(Model model) {
        
        if (!CheckExistModel(model.getIdHinh()))
        {
        	SQLiteDatabase db = this.getWritableDatabase();
             
	        ContentValues values = new ContentValues();
	        values.put(EConstant.KEY_ID_HINH, model.getIdHinh());
	        values.put(EConstant.KEY_VAN_BAN, model.getVanBan());
	        values.put(EConstant.KEY_Y_NGHIA, model.getYNghia());
	        values.put(EConstant.KEY_URI_HINH, model.getUriHinh());
	        values.put(EConstant.KEY_PHIEN_AM_QUOC_TE, model.getPhienAmQuocTe());
	        values.put(EConstant.KEY_LOAI_HINH, model.getLoaiHinh());
	        values.put(EConstant.KEY_LEVEL, model.getLevel());
	        values.put(EConstant.KEY_MA_CHU_DE, model.getMaChuDe());
	        values.put(EConstant.KEY_DATE_CREATE, model.getDateCreate());
	        
	        // updating row
	        return db.update(EConstant.TABLE, values, EConstant.KEY_ID_HINH + " = ?",
	                new String[] { String.valueOf(model.getIdHinh()) });
        }
        
        return -1;
    }
    
    /*
     * Deleting a todo
     */
    public void deleteModel(String model_id) {
        
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(EConstant.TABLE, EConstant.KEY_ID_HINH + " = ?",
                        new String[] { String.valueOf(model_id) });
    }
    
    // closing database
    public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }
    
    /**
     * get datetime
     * */
    public String getDateTime() {
        SimpleDateFormat dateFormat = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss", Locale.getDefault());
        Date date = new Date();
        return dateFormat.format(date);
    }
}